package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class QUserDao
{
  static Logger logger = LoggerFactory.getLogger(QUserDao.class);
  public static final String TABLE = "da_q_user";
  public static final String TABLE1 = "da_q_ek_user";
  
  public static boolean insert(QUser bean)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    
    sql = "INSERT INTO da_q_user (userid,groupid,username,passwd,yhlx,khrq,status,cxrq,ghrq,cxqx,dyqx,bz,realname,yhsf) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, bean.getUserId());
      pstmt.setString(i++, bean.getGroupId());
      pstmt.setString(i++, bean.getUserName());
      pstmt.setString(i++, bean.getPasswd());
      pstmt.setString(i++, bean.getYhlx());
      if ((bean.getKhrq() == null) || (bean.getKhrq().equals(""))) {
        pstmt.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
      } else {
        pstmt.setTimestamp(i++, 
          new Timestamp(bean.getKhrq().getTime()));
      }
      pstmt.setString(i++, bean.getStatus());
      if ((bean.getCxrq() == null) || (bean.getCxrq().equals(""))) {
        pstmt.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
      } else {
        pstmt.setTimestamp(i++, 
          new Timestamp(bean.getCxrq().getTime()));
      }
      if ((bean.getGhrq() == null) || (bean.getGhrq().equals(""))) {
        pstmt.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
      } else {
        pstmt.setTimestamp(i++, 
          new Timestamp(bean.getGhrq().getTime()));
      }
      pstmt.setString(i++, bean.getCxqx());
      pstmt.setString(i++, bean.getDyqx());
      pstmt.setString(i++, bean.getBz());
      
      pstmt.setString(i++, bean.getRealName());
      pstmt.setString(i++, bean.getYhsf());
      
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static boolean update(QUser bean)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    //xuning add 20141212
    //sql = "update da_q_user set groupid=?,username=?,passwd=?,status=?,cxrq=?,ghrq=?,cxqx=?,dyqx=?,bz=?,realname=?,yhsf=?,dyfs=?";
    sql = "update da_q_user set groupid=?,username=?,passwd=?,status=?,cxrq=?,ghrq=?,cxqx=?,dyqx=?,bz=?,realname=?,yhsf=?,dyfs=?,cxmd=?,djid=?";
    
    sql = sql + 
      " where trim(userid)=?";
    logger.debug("update sql:" + sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, bean.getGroupId().trim());
      pstmt.setString(i++, bean.getUserName());
      pstmt.setString(i++, bean.getPasswd());
      pstmt.setString(i++, bean.getStatus());
      if ((bean.getCxrq() == null) || (bean.getCxrq().equals(""))) {
        pstmt.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
      } else {
        pstmt.setTimestamp(i++, 
          new Timestamp(bean.getCxrq().getTime()));
      }
      if ((bean.getGhrq() == null) || (bean.getGhrq().equals(""))) {
        pstmt.setTimestamp(i++, new Timestamp(System.currentTimeMillis()));
      } else {
        pstmt.setTimestamp(i++, 
          new Timestamp(bean.getGhrq().getTime()));
      }
      pstmt.setString(i++, bean.getCxqx());
      pstmt.setString(i++, bean.getDyqx());
      pstmt.setString(i++, bean.getBz());
      
      pstmt.setString(i++, bean.getRealName());
      pstmt.setString(i++, bean.getYhsf());
      pstmt.setString(i++, bean.getDyfs());
      //xuning add 20141212
      pstmt.setString(i++, bean.getCxmd());
      //xuning add 20150122
      pstmt.setString(i++, bean.getDjid());
      
      pstmt.setString(i++, bean.getUserId().trim());
      
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static boolean updatePassword(String userId,String password)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    //xuning add 20141212
    //sql = "update da_q_user set groupid=?,username=?,passwd=?,status=?,cxrq=?,ghrq=?,cxqx=?,dyqx=?,bz=?,realname=?,yhsf=?,dyfs=?";
    sql = "update da_q_user set passwd=?";
    
    sql = sql + 
      " where userid=?";
    logger.debug("update sql:" + sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, password);
      pstmt.setString(i++, userId);
      pstmt.execute();


      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static boolean delete(String userName)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "DELETE FROM da_q_user WHERE username=? ";
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, userName);
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    
    return false;
  }
  
  public static QUser load(String userId)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "select * from da_q_user where trim(userid) = '" + 
      userId.trim() + "' ";
    logger.debug(userId.trim());
    logger.debug(sql);
    QUser quser = null;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        quser = rsToBean(rs);
      }
      QUser localQUser1 = quser;
      return localQUser1;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return null;
  }
  
  public static QUser loadBy(String userName)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT * FROM da_q_user WHERE status!='3' and username = ?  ";
    logger.debug(sql);
    QUser quser = null;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      pstmt.setString(i++, userName);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        quser = rsToBean(rs);
        logger.debug("find the user:{}",quser.toString());
      }else{
        logger.debug("not find the user:{}",userName);
      }
      return quser;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freeResultSet(rs);
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
    }
    
    return null;
  }
  
  public static ResultSet loadByName(String userName)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = 
      "SELECT * FROM da_q_user WHERE status!='3' and userName = '" + 
      userName + "'";
    logger.debug(sql);
    connsyb = SpringContextHolder.getBean(ConnectDB.class);
    
    try
    {
    	conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
    return rs;
  }
  
  public static List load(String userName, String status, String yhlx, int page, int pageSize, String djjg)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT * FROM (SELECT rownum r,s.* from (select A.*,B.username ekusername,B.Full_Name as fullname,c.content as cxqxname FROM da_q_user A,da_q_ek_user B,bm_cxqx c WHERE 1=1 AND B.USERID=A.USERID and A.cxqx=c.code(+) ";
    if (djjg != null) {
      sql = sql + "and B.unit like '" + djjg + "%'";
    }
    if ((userName != null) && (userName.length() > 0)) {
      sql = 
        sql + " AND B.userName LIKE ? ";
    }
    if ((status != null) && (status.length() > 0)) {
      sql = 
        sql + " AND A.status = ? ";
    }
    if ((yhlx != null) && (yhlx.length() > 0)) {
      sql = 
        sql + " AND A.yhlx = ? ";
    }
    if (pageSize > 1) {
      sql = 
        sql + "order by khrq desc) s where rownum <=? * ?  )t WHERE t.r > (? - 1) * ? ";
    }
    try
    {
      logger.debug("sql:{}",sql);
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userName + "%");
      }
      if ((status != null) && (status.length() > 0)) {
        pstmt.setString(i++, status);
      }
      if ((yhlx != null) && (yhlx.length() > 0)) {
        pstmt.setString(i++, yhlx);
      }
      if (pageSize > 1)
      {
        pstmt.setInt(i++, page);
        pstmt.setInt(i++, pageSize);
        pstmt.setInt(i++, page);
        pstmt.setInt(i++, pageSize);
      }
      rs = pstmt.executeQuery();
      List list = new ArrayList();
      while (rs.next()) {
        list.add(rsToBean1(rs));
      }
      logger.debug(
        list.size() + "size");
      List localList1 = list;
      return localList1;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return null;
  }
  
  public static int count(String userName, String status, String yhlx, String djjg)
  {
	  int cnt;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT count(*) FROM da_q_user B,da_q_ek_user A WHERE A.USERID=B.USERID ";
    if (djjg != null) {
    	if(djjg.endsWith("00")){
    		djjg = djjg.substring(0,4)+"%";
    	}else{
    	  djjg +="%";
        }
      sql = sql + " and A.unit like '" + djjg + "'";
    }
    if ((userName != null) && (userName.length() > 0)) {
      sql = 
        sql + " AND A.username LIKE ? ";
    }
    if ((status != null) && (status.length() > 0)) {
      sql = 
        sql + " AND B.status = ? ";
    }
    if ((yhlx != null) && (yhlx.length() > 0)) {
      sql = 
        sql + " AND B.yhlx = ? ";
    }
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;
      if ((userName != null) && (userName.length() > 0)) {
        pstmt.setString(i++, 
          "%" + userName + "%");
      }
      if ((status != null) && (status.length() > 0)) {
        pstmt.setString(i++, status);
      }
      if ((yhlx != null) && (yhlx.length() > 0)) {
        pstmt.setString(i++, yhlx);
      }
      rs = pstmt.executeQuery();
      if (rs.next()) {
    	  cnt = rs.getInt(1);
    	  return cnt;
      }
      
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    
    return 0;
  }
  
  static QUser rsToBean(ResultSet rs)
    throws SQLException
  {
    QUser bean = new QUser();
    bean.setBz(rs.getString("bz"));
    bean.setCxqx(rs.getString("cxqx"));
    
    bean.setCxrq(rs.getTimestamp("cxrq"));
    bean.setDyqx(rs.getString("dyqx"));
    bean.setGhrq(rs.getDate("ghrq"));
    bean.setGroupId(rs.getString("groupid")==null?"":rs.getString("groupid").trim());
    bean.setKhrq(rs.getDate("khrq"));
    bean.setPasswd(rs.getString("passwd"));
    bean.setStatus(rs.getString("status"));
    bean.setUserId(rs.getString("userid").trim());
    bean.setUserName(rs.getString("username"));
    bean.setYhlx(rs.getString("yhlx"));
    bean.setRealName(rs.getString("realname"));
    bean.setYhsf(rs.getString("yhsf"));
    bean.setDyfs(rs.getString("dyfs"));
    
    //xuning add 20141211
    bean.setCxmd(rs.getString("cxmd"));
    //xuning add 20150122
    bean.setDjid(rs.getString("djid"));
    return bean;
  }
  
  static QUser rsToBean1(ResultSet rs)
    throws SQLException
  {
    QUser bean = new QUser();
    
    bean.setBz(rs.getString("bz"));
    bean.setCxqx(rs.getString("cxqx"));
    bean.setCxrq(rs.getTimestamp("cxrq"));
    bean.setDyqx(rs.getString("dyqx"));
    bean.setGhrq(rs.getDate("ghrq"));
    bean.setGroupId(rs.getString("groupid")==null?"":rs.getString("groupid").trim());
    bean.setKhrq(rs.getDate("khrq"));
    bean.setPasswd(rs.getString("passwd"));
    bean.setStatus(rs.getString("status"));
    bean.setUserId(rs.getString("userid").trim());
    bean.setUserName(rs.getString("ekusername"));
    bean.setYhlx(rs.getString("yhlx"));
    bean.setQ_userName(rs.getString("username"));
    bean.setFullname(rs.getString("fullname"));
    bean.setRealName(rs.getString("realname"));
    bean.setYhsf(rs.getString("yhsf"));
    bean.setCxqxName(rs.getString("cxqxname"));
    return bean;
  }
  
  public static int count_yh()
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "SELECT count(*) FROM (SELECT rownum r, A.*,B.username ekusername,B.Full_Name as fullname FROM da_q_user A,da_q_ek_user B WHERE 1=1 and B.USERID=A.USERID ) ";
    
    logger.debug(sql);
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        return rs.getInt(1);
      }
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    DbClose.freePreparedStatement(pstmt);
    DbClose.freeConnection(conn);
    DbClose.freeResultSet(rs);
    
    return 0;
  }
  
  //xuning add 20150118
  public static QUser login(String userName,String password)
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "select trim(a.userid) as userid,trim(a.groupid) as groupid,a.username as username,a.passwd as passwd,a.yhlx as yhlx,a.status as status,a.khrq as khrq,a.cxrq as cxrq,a.ghrq as ghrq,a.cxqx as cxqx,a.dyqx as dyqx,a.bz as bz,a.realname,a.yhsf,a.dyfs,a.cxmd,b.unit as unit,b.department as department,a.djid from da_q_ek_user b,da_q_user a where a.userid = b.userid and a.status='2'";
    sql += " and a.username=? and (a.passwd is null or a.passwd=? )";
    logger.debug(sql);
    QUser quser = null;
    try
    {
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, userName);
      pstmt.setString(2, password);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        quser = rsToBean(rs);
      }
      return quser;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    
    return null;
  }

  public static List loadList() {

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    ConnectDB connsyb = null;
    sql = "select A.*,B.username ekusername,B.Full_Name as fullname,c.content as cxqxname FROM da_q_user A,da_q_ek_user B,bm_cxqx c WHERE 1=1 AND B.USERID=A.USERID and A.cxqx=c.code(+)  ";

    try
    {
      logger.debug("sql:{}",sql);
      connsyb = SpringContextHolder.getBean(ConnectDB.class);
      conn = connsyb.getConnection();
      pstmt = conn.prepareStatement(sql);
      int i = 1;

      rs = pstmt.executeQuery();
      List list = new ArrayList();
      while (rs.next()) {
        list.add(rsToBean1(rs));
      }
      logger.debug(
              list.size() + "size");
      List localList1 = list;
      return localList1;
    }
    catch (SQLException e)
    {
      logger.info("db error", e);
    }
    finally
    {
      DbClose.freePreparedStatement(pstmt);
      DbClose.freeConnection(conn);
      DbClose.freeResultSet(rs);
    }
    return null;
  }
}
